import random
import os
import pymysql
from setting.mysql_test import mysql45_config

# 根据爬下来的图片在本地进行搜索，tag——info没有的话，从通用图库获取
FOLDER_PATH = r'E:\workspace\kyk.com\gpt_auto_video\backend\static\pictures\examples\job1'
GENERIC_PATH = r'F:\image\generic_image'


class ImageProduce(object):
    def __init__(self, labels):
        self.mysql_conn = pymysql.connect(**mysql45_config)
        self.mysql_cursor = self.mysql_conn.cursor()
        self.labels = labels

    def get_image_path(self):
        label = self.labels.split(' ')
        image_paths = []
        for l in label:
            #统一转换为小写
            l = l.lower()
            print(l)
            select_sql = "SELECT COUNT(image_id) c, tag_id FROM image_tag_relationship  WHERE tag_id IN(SELECT id FROM tag_info WHERE tag LIKE '%%%s%%') GROUP BY tag_id ORDER BY c DESC LIMIT 1" % l
            self.mysql_cursor.execute(select_sql)
            tag_list = self.mysql_cursor.fetchall()
            tag_id = tag_list[0][1]
            select_image_sql = "SELECT i.id, i.search_key, i.oss_image_url, i.download_status, r.tag_id, t.tag, t.chinese_tag " \
                               "FROM image_info i LEFT JOIN image_tag_relationship r on i.id = r.image_id " \
                               "LEFT JOIN tag_info t ON r.tag_id = t.id WHERE tag_id = '%s' AND download_status = 1" % tag_id
            self.mysql_cursor.execute(select_image_sql)
            image_list = self.mysql_cursor.fetchall()
            if not image_list:
                print('标签%s没有图片' % l)
                continue
            image_ids = []
            for image in image_list:
                image_id = image[0]
                image_ids.append(image_id)
            num = len(image_ids)
            if num < 3:
                print('标签%s的图片数量小于3，选择一个随机id' % l)
                random_image_ids = random.sample(image_ids, 1)
                select_image_url_sql = "SELECT local_path FROM image_info WHERE id = %s" % (random_image_ids[0],)
                self.mysql_cursor.execute(select_image_url_sql)
                image_path_list = self.mysql_cursor.fetchone()
                path = image_path_list[0]
                image_paths.append(path)
            else:
                random_image_ids = random.sample(image_ids, 3)
                print('随机获取的image_ids', random_image_ids)
                select_image_url_sql = "SELECT local_path FROM image_info WHERE id IN (%s, %s, %s)" % (
                random_image_ids[0], random_image_ids[1], random_image_ids[2])
                self.mysql_cursor.execute(select_image_url_sql)
                image_path_list = self.mysql_cursor.fetchall()
                for path in image_path_list:
                    path = path[0]
                    image_paths.append(path)

        return image_paths

    def save_image_path(self, image_paths):
        for image_path in image_paths:
            os.system('cp %s %s' % (image_path, FOLDER_PATH))

    def get_generic_photo(self):
        # 获取通用图片
        generic_image_path = []
        for filename in os.listdir(GENERIC_PATH):
            generic_image = os.path.join(GENERIC_PATH, filename)
            generic_image_path.append(generic_image)
        num = len(generic_image_path)
        print('通用图片数量', num)
        # 随机获取三张通用图片
        generic_image_paths = random.sample(generic_image_path, 3)
        print('随机获取的通用图片路径', generic_image_paths)

    def __del__(self):
        self.mysql_cursor.close()
        self.mysql_conn.close()



labels = 'finance  technology'
unsplash = ImageProduce(labels)
#image_paths = unsplash.get_image_path()
#unsplash.save_image_path(image_paths)
unsplash.get_generic_photo()





